import pymysql


class Connection(object):

    def __init__(self, ip, user, passwd, database, port=3306):
        self.ip = ip
        self.user = user
        self.passwd = passwd
        self.database = database
        self.port = port
        i = 1
        while True:
            if i > 3:
                print('连接数据库失败')
                break
            try:
                self.db = self.connect()
                break
            except:
                i += 1
        if not self.db:
            return '连接数据库失败'
        self.cursor = self.db.cursor()

        self.wheresPlural = ' where '
        self.fields = '*'
        self.wheres = ''
        self.limits = ''

        self.sqlListen = ''

    def connect(self):
        return pymysql.Connect(
            host=self.ip,  # mysql的主机ip
            port=self.port,  # 端口
            user=self.user,  # 用户名
            passwd=self.passwd,  # 数据库密码
            db=self.database,  # 数据库名
            charset='utf8',  # 字符集
        )

    '''
        conditions 条件字段
        value      条件取值
        ifoperator   判断运算符
        @return  self
    '''

    def where(self, conditions, operator, value=''):
        if not value:
            value = operator
            operator = '='

        if type(value) is str:
            value = "'" + value + "'"

        self.wheresPlural += conditions + ' ' + operator + ' ' + str(value) + ' and '
        self.wheres = self.wheresPlural[0:-5]
        return self

    '''
        要查询的字段
        @return  self
    '''

    def field(self, fields):
        self.fields = fields
        return self

    '''
        偏移量查询

        start 从第几条开始
        offset 每次获取条数
        @return  self
    '''

    def limit(self, start, offset):
        self.limits = ' limit ' + str(start) + ',' + str(offset)
        return self

    '''
        处理单条添加语句参数
        key_str 添加字段
        key_value 字段对应值
        @return  string 
    '''

    def insertParam(self, dictObj):
        key_str = ''
        key_value = ''
        for key in dictObj:
            key_str += key + ','
            if type(dictObj[key]) is str:
                key_value += "'" + dictObj[key] + "',"
            else:
                key_value += str(dictObj[key]) + ","

        return key_str[0:-1], key_value[0:-1]

    '''
        处理多条添加语句参数
        key_str 添加字段
        key_value 字段对应值
        @return  string 
    '''

    def insertParamAll(self, dictObjAll):
        key_str = ''
        key_value = ''
        for key in dictObjAll[0]:
            key_str += key + ','
        for i in range(len(dictObjAll)):
            dictObj = dictObjAll[i]
            values = ''
            for keys in dictObj:
                if type(dictObj[key]) is str:
                    values += "'" + str(dictObj[keys]) + "',"
                else:
                    values += str(dictObj[keys]) + ","

            key_value += '(' + values[0:-1] + '),'

        return key_str[0:-1], key_value[0:-1]

    def __del__(self):
        print('close')
        self.cursor.close()
